Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

Using the block cursor switches

The DataServer provides one switch to enable or disable block cursors, and two switches to regulate memory consumption by block cursors. Block cursors also interact with the query tuning cache size allocations.

Block cursors: The -Dsrv PRGRS_BLOCK_CURS,1 option

Block cursors are ON (1) by default. To disable block cursors for the entire connection, set this option to OFF (0). The connection-level setting can be overridden for a specific query with the query-tuning option: QUERY-TUNING(NO-LOOKAHEAD).

Block cursor allocation by table: The -Dsrv PRGRS_TABLE_BLOCKSIZE,nnn option

The -Dsrv PRGRS_TABLE_BLOCKSIZE,nnn option where nnn is the size in bytes, indicates the total allowable memory available to cursors of a given table. This value puts a cap on the amount of memory all the open cursors against a given table can consume. It does not determine an amount to be allocated. This upper limit on memory allocation ensures that memory is not overutilized.

ODBC_DEF_TABBLOCKSZ is the default value for PRGRS_TABLE_BLOCKSIZE. It is currently set at 65,000 bytes. At this rate approximately 18 tables open and utilizing all available cursor space for block cusrors would utilize just 1 MB of memory. If record sizes are large or the typical row count of your result sets is large and you have plenty of avilable memory and your open table count is not too large, you might consider increasing this size. If you are using served OpenEdge clients, keep in mind that the memory requirments of the DataServer server executable on the server machine are compounded by the number of users attached to the database through the same broker.

Total Block cursor allocation: The -Dsrv PRGRS_MAX_BLOCKSIZE,nnn option

The -Dsrv PRGRS_MAX_BLOCKSIZE,nnn option where nnn is the size in bytes, indicates the total allowable memory available to all cursors established as block cursors. This value limits the total memory available to all block cursors irrespective of the table to which it is allocated. ODBC_DEF_MAXBLOCKSZ sets the default size which is currently 1MB. The overall memory allocated to block cursors by the DataServer connection cannot exceed the value set for PRGRS_MAX_BLOCKSIZE. Any block cursor that will place memory consumption over this threshold will be rejected as a block cursor and instead be established as a lookahead cursor.

Impact of block cursors on cache size: The -Dsrv QT_CACHE_SIZE,nnn option

The -Dsrv QT_CACHE_SIZE,nnn where nnn is the size in bytes of the cache to be allocated for a cursor’s result set. This is a connection level default that can be overridden at the query level. If the connection level cache size is not overridden at the query level, then the query cache size times the number of open cursors cannot exceed the maximum block areas for the table set by the -Dsrv PRGRS_TABLE_BLOCKSIZE switch at any given time. The accumulation of query cache sizes from each block cursor that has been allocated cannot exceed the total maximum block areas available to block cursors as set by the -Dsrv PRGRS_MAX_BLOCKSIZE switch. If either block cursor limit is reached, cursors will downgrade to lookahead cursoring. There is no minimum for this value, however if two or more records cannot be returned to the established block, a lookahead cursor is used. The query tuning cache size should be set higher than the maximum record size times two to prevent cursor downgrades.

ODBC_DEF_BLKCACHESZ is the default value for QT_CACHE_SIZE when block cursors are enabled. This value is currently set to 10,000 bytes.

ODBC_DEF_LHDCACHESZ is the default value of QT_CACHE_SIZE when lookahead cursors are enabled. It is currently set at 30,000 bytes.

The value of QT_CACHE_SIZE represents an upper limit for the row space available to the lookahead cache, not the amount of space a lookahead cache will actually use. This highlights a key distinction between block and lookahead cursors. The “cache size” for block cursors is preallocated before results are retrieved so this value represents an actual allocated amount. For lookahead cursors, memory is accrued as rows are read back from the result set and added to the cache. So the “cache size” specifies an upper limit on the number of cached rows allowable, not an exact amount of space that will be allocated as is the case with block cursors.

Adjusting values

With the default size, approximately 6.5 open cursors and result sets per table can be established at a given time before the maximum block memory area (PRGRS_TABLE_BLOCKSIZE) is exceeded. At 10,000 bytes, 104 new block allocations can be established before the maximum block memory (PRGRS_MAX_MBLOCKSIZE) is exceeded. If record sizes are large or the typical row counts for your results sets are large, you can consider increasing one or both of these sizes. Conversely, if record sizes are small or the typical row counts for your results are small, you can consider decreasing theses sizes. Consider setting these tuning parameters based on your typical requirements and then override them for specific queries that are the exceptions using the query tuning option.